﻿/* 
 * Chunks of this were inspired by Sacha Tomey's MDXHelper class.
 * http://blogs.adatis.co.uk/blogs/sachatomey/archive/2007/11/20/mdx-stored-procedures-part-2.aspx
 */

using System;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Caching;
using System.Xml;
using Microsoft.AnalysisServices.AdomdClient;
using System.Web.Configuration;
using System.Data.SqlClient;

namespace Symantec.DataFactories
{

    /// <summary>
    /// Represents a set of methods for creating instances of the Microsoft.AnalysisServices.AdomdClient
    /// providor's implementation of the data source classes.
    /// </summary>
    public sealed class AdomdClientDataFactory : IDataFactory
    {
        #region Member variables
        private AdomdCommand _command;
        private AdomdConnection _connection;
        private AdomdDataAdapter _dataAdapter;
        private string _storedProceduresPath;
        #endregion

        #region Private constants
        private const bool SupportsXmlreader = true;
        private const string CacheKeyPrefix = "AdomdClientDataFactory:";
        private const int CacheSlidingExpirationMinutes = 30;
        #endregion

        /// <summary>
        /// Initializes a new instance of a Symantec.DataFactories.AdomdClientFactory class.
        /// </summary>
        private AdomdClientDataFactory() { }

        /// <summary>
        /// Gets an instance of the IDataFactory. This can be used to retrieve strongly 
        /// typed data objects.
        /// </summary>
        public static IDataFactory Instance
        {
            get
            {
                return new AdomdClientDataFactory();
            }
        }

        #region IDataFactory Members
        /// <summary>
        /// Creates a new IDbCommand object.
        /// </summary>
        /// <returns>
        /// A new, strongly typed instance of IDbCommand.
        /// </returns>
        public IDbCommand CreateCommand()
        {
            if (_connection == null)
                _connection = new AdomdConnection();

            return _command ?? (_command = _connection.CreateCommand());
        }

        /// <summary>
        /// Creates a new IDbConnection object.
        /// </summary>
        /// <returns>
        /// A new, strongly typed instance of IDbConnection.
        /// </returns>
        public IDbConnection CreateConnection()
        {
            return _connection ?? (_connection = new AdomdConnection());
        }

        /// <summary>
        /// Creates a new IDbDataAdapter object.
        /// </summary>
        /// <returns>
        /// A new, strongly typed instance of IDbDataAdapter.
        /// </returns>
        public IDataAdapter CreateDataAdapter()
        {
            if (_dataAdapter == null)
                _dataAdapter = new AdomdDataAdapter();
            _dataAdapter.SelectCommand = _command;
            return _dataAdapter;
        }

        /// <summary>
        /// Check if this IDataFactory class supports System.Xml.XmlReader as not all IDataFactories 
        /// might.
        /// </summary>
        public bool SupportsXmlReader
        {
            get { return SupportsXmlreader; }
        }

        /// <summary>
        /// Use to execute the command and return a System.Xml.XmlReader object as IDbCommand does
        /// not support ExecuteXmlReader().
        /// </summary>
        /// <returns>
        /// Returns a System.Xml.XmlReader object.
        /// </returns>
        public XmlReader ExecuteXmlReader()
        {
            // Keep the original stored procedure name to be restored after executing the 
            // query stored in that file.
            AdomdCommand originalCommand = _command;

            if (_command.CommandType == CommandType.StoredProcedure)
            {
                GetStoredProcedure();
            }
            ApplyParameters();

            // Run the query.
            XmlReader xmlReader = _command.ExecuteXmlReader();

            // Restore the original command (stored procedure name if a CommandType.StoredProcedure).
            _command = originalCommand;

            // Return the XmlReader object.
            return xmlReader;
        }

        /// <summary>
        /// Use to execute the command and return a strongly typed System.Data.IDataReader as the 
        /// results from AdomdCommand.ExecuteReader() is filled with Objects.
        /// </summary>
        /// <returns>
        /// Returns a System.Data.IDataReader object.
        /// </returns>
        public IDataReader ExecuteReader()
        {
            // Get the data and save it as a data table.
            DataSet queryResults = new DataSet();
            Fill(queryResults);

            // Return the table as a data reader.
            return queryResults.CreateDataReader();
        }

        /// <summary>
        /// Use to execute the command and fill a supplied DataSet object with the query results,
        /// returning the number of rows added to the DataSet.
        /// </summary>
        /// <param name="result">
        /// DataSet object to fill.
        /// </param>
        /// <returns>
        /// Returns the count of rows added to the DataSet.
        /// </returns>
        public int Fill(DataSet result)
        {
            int countRowsAdded;

            // Run the query stored in the command object.
            if (_connection.State != ConnectionState.Open)
                _connection.Open();

            using (XmlReader xmlReader = ExecuteXmlReader())
            {
                // Convert the results from XML to a strongly typed data table.
                result.Tables.Add(ConvertXmlResultSet(xmlReader));

                // How many rows added to the data table?
                int tableCount = result.Tables.Count;
                countRowsAdded = result.Tables[tableCount - 1].Rows.Count;
            }

            return countRowsAdded;
        }
        #endregion

        /// <summary>
        /// Reads the ASPROC file from the file system.
        /// </summary>
        private void GetStoredProcedure()
        {
            // Will hold the XML file storing the MDX stored procedure.
            XmlDocument storedProcedure = new XmlDocument();

            // The name of the stored procedure is also the file name sans the path and .asproc extension.
            string storedProcedureName = _command.CommandText;

            #region Read the ASPROC file
            // Is this a web app?
            #region Web app code
            if (HttpContext.Current != null)
            {
                // Check the cache first as the file may have already been read.
                string cacheKey = string.Format("{0}{1}", CacheKeyPrefix, storedProcedureName);
                if (HttpContext.Current.Cache[cacheKey] == null)
                {
                    // We've not already read in the file; let's do this now and cache it for next time.

                    string fileContents = string.Empty;

                    // Read the file and cache the query
                    XmlDocument xmlFile = new XmlDocument();
                    xmlFile.Load(AbsoluteUrlUsingAuthority);
                    fileContents = xmlFile.InnerXml;

                    // Cache the query
                    HttpContext.Current.Cache.Insert(cacheKey, fileContents, null, Cache.NoAbsoluteExpiration, new TimeSpan(0, CacheSlidingExpirationMinutes, 0));
                }

                // Load the file from the cache into an XmlDocument to parse out query and parameters.
                storedProcedure.LoadXml(HttpContext.Current.Cache[cacheKey].ToString());
            }
            #endregion
            // Any application type other than a web app runs this.
            #region Windows, console, etc., app code
            else
            {
                // Try to get the path to ADOMD stored procedures from the user settings file.
                _storedProceduresPath = Properties.Settings.Default.StoredProceduresPath;

                // If not there, use a folder named AdomdStoredProcedures in the application folder.
                if (_storedProceduresPath == string.Empty)
                {
                    // Get the location of the DLL.
                    string appPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);

                    // Assumption: This DLL will reside either in the application folder directly or
                    // in a BIN (bin) folder under the application's folder.

                    // Trim off the BIN (and anything else, like DEBUG).
                    if (appPath.ToUpper().LastIndexOf("BIN") >= 0)
                        appPath = appPath.Remove(appPath.ToUpper().LastIndexOf("BIN"));

                    _storedProceduresPath = appPath.TrimStart("file:\\".ToCharArray()) + "AdomdStoredProcedures";

                    // Store result in user settings file.
                    Properties.Settings.Default.StoredProceduresPath = _storedProceduresPath;
                    Properties.Settings.Default.Save();
                }

                // Read the contents of the file as XML.
                XmlTextReader storedProcedureFile =
                    new XmlTextReader(string.Format("{0}\\{1}.xml", _storedProceduresPath, storedProcedureName))
                    {
                        WhitespaceHandling = WhitespaceHandling.None
                    };
                storedProcedure.Load(storedProcedureFile);
                storedProcedureFile.Close();
            }
            #endregion
            #endregion

            #region Parse ASPROC file
            // Get the query out of the XML.
            XmlNode queryNode = storedProcedure.SelectSingleNode("/AdomdStoredProcedure/query");
            _command.CommandText = queryNode.InnerText;

            // Get the parameter names out of the XML.
            XmlNodeList xnList = storedProcedure.SelectNodes("/AdomdStoredProcedure/parameters/parameter");
            #endregion

            #region Validate parameters
            // Compare the number of parameters supplied with the number expected.
            if (xnList.Count != _command.Parameters.Count)
                throw new ApplicationException("Parameter mismatch: please check the name and number of parameters supplied to the stored procedure.");

            // Compare names in stored procedure with command.Parameters.
            foreach (XmlNode parameter in xnList)
            {
                // Provides support for both storing the parameter names as either attributes or text within the tag.
                string parameterName = parameter.Attributes != null && parameter.Attributes["name"] != null
                    ? parameter.Attributes["name"].Value : parameter.InnerText.Trim();
                if (_command.Parameters[parameterName] == null)
                    throw new ApplicationException("Parameter mismatch: please check the name and number of parameters supplied to the stored procedure.");
            }
            #endregion
        }

        private string AbsoluteUrlUsingAuthority
        {
            get
            {
                string authority = HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority);
                string appPath = HttpContext.Current.Request.ApplicationPath.TrimEnd('/');
                return string.Format("{0}{1}/AdomdStoredProcedures/{2}.xml",
                                     authority,
                                     appPath,
                                     _command.CommandText);
            }
        }

        /// <summary>
        /// Applies the parameters from this._command to the query.
        /// </summary>
        private void ApplyParameters()
        {
            // Loop through the command's parameters.
            foreach (AdomdParameter parameter in _command.Parameters)
            {
                // Reformats @parameter to (?<=@)\bparameter\b to prepare for Regex.
                string regEx = String.Format("(?<=@){0}{1}{0}", @"\b", parameter.ParameterName.Replace("@", string.Empty));
                // Replaces the parameter in the command with the parameter value, leaving behind the @ sign
                _command.CommandText = Regex.Replace(_command.CommandText, regEx, parameter.Value.ToString(), RegexOptions.IgnoreCase);
            }
            // Remove the vestigial @ signs
            _command.CommandText = _command.CommandText.Replace("@", string.Empty);
        }

        /// <summary>
        /// Converts the XML results from the query to a DataTable.
        /// </summary>
        /// <param name="xmlReader">
        /// The XmlReader query results.
        /// </param>
        /// <returns>
        /// Returns a strongly typed DataTable.
        /// </returns>
        private static DataTable ConvertXmlResultSet(XmlReader xmlReader)
        {
            #region Load XML Document
            // Load in the XML reader into an XML document
            XmlDocument xml = new XmlDocument();
            xml.Load(xmlReader);

            // Setup the namespace managers

            // Default
            XmlNamespaceManager nsManager = new XmlNamespaceManager(xml.NameTable);
            nsManager.AddNamespace("defaultNS", "urn:schemas-microsoft-com:xml-analysis:mddataset");
            nsManager.AddNamespace("xsi", "http://www.w3.org/2001/XMLSchema-instance");
            nsManager.AddNamespace("xsd", "http://www.w3.org/2001/XMLSchema");

            // Exception
            XmlNamespaceManager nsExceptionManager = new XmlNamespaceManager(xml.NameTable);
            nsExceptionManager.AddNamespace("emptyNS", "urn:schemas-microsoft-com:xml-analysis:empty");
            nsExceptionManager.AddNamespace("exceptionNS", "urn:schemas-microsoft-com:xml-analysis:exception");
            #endregion

            // Check for any query exceptions
            XmlNode errorNode = xml.SelectSingleNode("emptyNS:root/exceptionNS:Messages/exceptionNS:Error", nsExceptionManager);
            if (errorNode != null && errorNode.Attributes != null)
            {
                // We have an error with the query, throw an application error
                throw new ApplicationException("MDX Query Returned Error: " + errorNode.Attributes["Description"].Value);
            }

            #region Build Data Table Definition
            // Build a data table with the appropriate columns and data types
            DataTable dataTable = new DataTable("Result");

            #region CellSet Rows
            // Obtain the row tuples
            XmlNodeList rowTuples = xml.SelectNodes("defaultNS:root/defaultNS:Axes/defaultNS:Axis[@name ='Axis1']/defaultNS:Tuples/defaultNS:Tuple", nsManager);

            // Check we have some rows before processing
            if (rowTuples.Count > 0)
            {
                // Handle Members in the first tuple
                // (We only need to look at the members in the first tuple as these will be repeated for each row)
                foreach (XmlNode member in rowTuples[0])
                {
                    // Obtain the column name and the data type
                    string columnName = member.Attributes["Hierarchy"].Value.Substring(member.Attributes["Hierarchy"].Value.LastIndexOf("[") + 1).Replace("]", string.Empty);
                    Type dataType = Type.GetType("System.String");

                    // Add a column
                    dataTable.Columns.Add(columnName, dataType);
                }
            }
            #endregion

            #region CellSet Columns
            // We need to look at all members for every tuple on columns

            // Obtain the column tuples
            XmlNode columnTuples = xml.SelectSingleNode("defaultNS:root/defaultNS:Axes/defaultNS:Axis[@name ='Axis0']/defaultNS:Tuples", nsManager);

            // Handle tuples on the column axis
            int baseColumnOrdinal = 0;
            foreach (XmlNode tuple in columnTuples.ChildNodes)
            {
                // Handle Members in the tuple
                // If there are multiple members then a cross join is being applied 
                // We will then need to merge the members to form a unique column name
                string columnName = string.Empty;
                foreach (XmlNode member in tuple.ChildNodes)
                {
                    // Append a hyphen if we have multiple members just to make the field name more distinguishable
                    if (columnName.Length > 0) columnName += "-";

                    // Append this member caption to the column name
                    columnName = columnName + member.SelectSingleNode("defaultNS:Caption", nsManager).InnerText;
                }

                // We can determine the data type of the column although this process can be overridden for
                // performance gains or when columns have multiple data types.
                Type dataType = Type.GetType("System.String");

                // We need to locate the data type of this column
                // Not all data may be present so we need to iterate the cellset data to find the first occurrence
                // of the data for this column to obtain the data type.

                // Obtain the cell data node
                XmlNode cells = xml.SelectSingleNode("defaultNS:root/defaultNS:CellData", nsManager);

                // Check we have some cells
                if (cells.ChildNodes.Count > 0)
                {
                    // Obtain the maximum cell ordinal
                    // (Nulls are not returned so this will not be the same as the final cell count in the table)
                    int maxCellOrdinal = Convert.ToInt32(cells.LastChild.Attributes["CellOrdinal"].Value);
                    int ordinalCursorOffset = columnTuples.ChildNodes.Count;

                    // Iterate ordinals, offsetting appropriately until we find one, or hit the end of the cellset
                    for (int ordinalCursor = baseColumnOrdinal; ordinalCursor <= maxCellOrdinal; ordinalCursor = ordinalCursor + ordinalCursorOffset)
                    {
                        // Obtain the data type from the actual data
                        XmlNode data = cells.SelectSingleNode("defaultNS:Cell[@CellOrdinal ='" + ordinalCursor + "']/defaultNS:Value", nsManager);

                        // Check we have some data
                        if (data != null)
                        {
                            // Extract the data type
                            XmlAttribute dataTypeAttribute = data.Attributes["xsi:type"];
                            if (dataTypeAttribute != null)
                            {
                                // Parse the data type contained within the xml file into something we can use
                                string dataTypeStringValue = dataTypeAttribute.Value.Replace("xsd:", string.Empty);
                                dataTypeStringValue = dataTypeStringValue.Replace(dataTypeStringValue.Substring(0, 1), dataTypeStringValue.Substring(0, 1).ToUpper());
                                if (dataTypeStringValue.CompareTo("Int") == 0) dataTypeStringValue += "32";
                                dataType = Type.GetType("System." + dataTypeStringValue);
                            }

                            // We've found one, exit the for loop !
                            break;
                        }
                    }
                }

                // Add a column
                dataTable.Columns.Add(columnName, dataType);
                baseColumnOrdinal++;
            }
            #endregion
            #endregion

            #region Populate Data Table
            // Populate the data table with the row information
            int cellSetOrdinal = 0;
            foreach (XmlNode tuple in rowTuples)
            {
                // Create a new row
                DataRow row = dataTable.NewRow();

                // Process each member within the current tuple
                int fieldIndex = 0;
                foreach (XmlNode member in tuple.ChildNodes)
                {
                    // Obtain the row data
                    row[fieldIndex] = member.SelectSingleNode("defaultNS:Caption", nsManager).InnerText;
                    fieldIndex++;
                }

                // Populate the remaining columns in the data table with data from the cell data collection
                for (int columnIndex = fieldIndex; columnIndex < dataTable.Columns.Count; columnIndex++)
                {
                    try
                    {
                        // Obtain the data node, either it's the raw data value or the formatted value as determined by the DataTableDataFormat property
                        string dataNodeXPath = "defaultNS:root/defaultNS:CellData/defaultNS:Cell[@CellOrdinal ='" + cellSetOrdinal + "']/defaultNS:";
                        dataNodeXPath += "Value";
                        XmlNode data = xml.SelectSingleNode(dataNodeXPath, nsManager);
                        if (data != null)
                            row[columnIndex] = data.InnerText;
                        else
                            row[columnIndex] = DBNull.Value;
                        cellSetOrdinal++;
                    }
                    catch (ArgumentException aex)
                    {
                        throw new ApplicationException("Error populating data.\n(" + aex.Message + ")\nConsider setting MDXHelper.BypassDataTyping property to true");
                    }
                }

                // Add the row to the data table
                dataTable.Rows.Add(row);
            }
            #endregion

            // Return data table
            return dataTable;
        }
    }
}
